﻿using System.Linq;
using System.Data.Linq;
namespace UniMana.Data
{
    public static class ReportScoreMajor
    {
        /// <summary>
        /// Lấy điểm sinh viên với ngành, khóa, năm học
        /// </summary>
        /// <param name="nScore"></param>
        /// <param name="nStudentClass"></param>
        /// <param name="nSubjectClass"></param>
        /// <param name="m_iMajorKey"></param>
        /// <param name="m_iCourseKey"></param>
        /// <param name="m_iSchoolYearKey"></param>
        /// <returns></returns>
        public static IQueryable GetScoreOfStudentByMajorCourseSchoolYear(
            Table<Training_Score> nScore,
            Table<Training_Student_Class> nStudentClass, 
            Table<Training_Subject_Class> nSubjectClass, 
            Table<Training_SubjectClass_ScoreType> nSubjectClassScoreType, 
            Table<Training_Student_Subject> nStudentSubject, 
            int m_iMajorKey, 
            int m_iCourseKey,
            int m_iSchoolYearKey)
        {
            var score = from t in nScore
                        join t0 in nSubjectClass
                        on t.Training_Subject_ClassKey equals t0.Training_Subject_ClassKey
                        join t1 in nSubjectClassScoreType
                              on new { t.Training_Subject_ClassKey, t.Training_ScoreTypeKey }
                          equals new { t1.Training_Subject_ClassKey, t1.Training_ScoreTypeKey }
                        join t2 in nStudentSubject
                              on t.Training_StudentKey
                          equals t2.Training_StudentKey
                        join t3 in nStudentClass on t.Training_StudentKey equals t3.Training_StudentKey
                        where
                          t0.Catalogue_SchoolYearKey == m_iSchoolYearKey &&
                          t3.Catalogue_MajorKey == m_iMajorKey &&
                          t3.Catalogue_CourseKey == m_iCourseKey &&
                          t0.Training_SubjectKey == t2.Training_SubjectKey &&
                          t0.Catalogue_TermKey == t2.Catalogue_TermKey &&
                          t0.Catalogue_SchoolYearKey == t2.Catalogue_SchoolYearKey
                        orderby
                          t.Training_StudentKey,
                          t0.Catalogue_TermKey,
                          t.Training_Subject_ClassKey,
                          t0.Training_SubjectKey
                        select new
                        {
                            t.Training_StudentKey,
                            Catalogue_TermKey = (System.Int32?)t0.Catalogue_TermKey,
                            t.Score,
                            t1.DefaultPercent,
                            t2.TotalCredits
                        };
            return score;
        }

        /// <summary>
        /// Lấy phần trăm điểm
        /// </summary>
        /// <param name="nSubjectClassScoreType"></param>
        /// <param name="m_iSubjectClassKey"></param>
        /// <param name="m_iScoreTypeKey"></param>
        /// <returns></returns>

        public static IQueryable GetPercentBySubjectClassKeyScoreTypeKey(
            this Table<Training_SubjectClass_ScoreType> nSubjectClassScoreType, 
            string m_strSubjectClassKey, 
            string m_strScoreTypeKey)
        {
            var percent = from t in nSubjectClassScoreType
                          where t.Training_Subject_ClassKey.ToString() == m_strSubjectClassKey
                          where t.Training_ScoreTypeKey.ToString() == m_strScoreTypeKey
                          select new
                          {
                              t.DefaultPercent
                          };
            return percent.Distinct();
        }

        /// <summary>
        /// Lấy tín chỉ cho môn
        /// </summary>
        /// <param name="nStudentSubject"></param>
        /// <param name="m_strStudentKey"></param>
        /// <param name="m_iSubjectKey"></param>
        /// <param name="m_iTermKey"></param>
        /// <returns></returns>

        public static IQueryable GetCreditByStudentKeySubjectKeyTermKey(
            this Table<Training_Student_Subject> nStudentSubject, 
            string m_strStudentKey, 
            string m_strSubjectKey, 
            string m_strTermKey)
        {
            var credit = from t in nStudentSubject
                         where t.Training_StudentKey.ToString() == m_strStudentKey
                         where t.Training_SubjectKey.ToString() == m_strSubjectKey
                         where t.Catalogue_TermKey.ToString() == m_strTermKey
                         select new
                         {
                             t.TotalCredits
                         };
            return credit.Distinct();
        }
    }
}
